<?php
	class ServiceConverter{
		public static function convert($id, $name, $descr, $active, $period, $price){
			$result = new Service();
			
			$result->setId($id);
			$result->setName($name);
			$result->setDesc($descr);
			$result->setPeriod($period);
			$result->setPrice($price);
			$result->setActive(VariableUtils::getBoolean($active));
			return $result;
		}
	}

	class ServiceFindCommand implements Command{
		private $controls;
		public function __construct(ServiceSearchControls $controls){
			$this->controls= $controls;
		}
		
		public function doExecute($connection){
			$result = array();
			
			
			$query = "select distinct st." .
				BoaConstans::SERVICE_COLUMN_ID . ", st." .
			BoaConstans::SERVICE_COLUMN_NAME   . ", st." .
			BoaConstans::SERVICE_COLUMN_DESCR  . ", st." .
			BoaConstans::SERVICE_COLUMN_ACTIVE . ", st." .
			BoaConstans::SERVICE_COLUMN_PERIOD . ", st." .
			BoaConstans::SERVICE_COLUMN_PRICE  .
			" from " . BoaConstans::SERVICE_TABLE . " st";
			
			$where = null;
			$types = "";
			$params = array();

			 if($this->controls->getName() != null){
			 	$types .="s";
				$params[] = $this->controls->getName();				
				$this->addToWhere($where, "upper(st." . BoaConstans::SERVICE_COLUMN_NAME . ") like upper(CONCAT('%', ?, '%'))");
			 }
			 
			 
			 if($this->controls->getPartName() != null){
			 	$types .="s";
			 	$params[] = $this->controls->getPartName();
			 	$partNameQuery = "st." . BoaConstans::SERVICE_COLUMN_ID . " in(select sspt." . BoaConstans:: SERVICE_SERVICE_PART_COLUMN_SID . " from " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " sspt where " . 
			 	BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . " in ( select spt." . BoaConstans::SERVICE_PART_COLUMN_ID . " from " . BoaConstans::SERVICE_PART_TABLE . " spt where upper(spt." . 
			 	BoaConstans::SERVICE_PART_COLUMN_NAME . ") like upper(CONCAT('%', ?, '%')) ))";
			 	$this->addToWhere($where ,$partNameQuery);
			 }
			 
			 if(($serviceTypes = $this->controls->getTypes()) != null && sizeof($serviceTypes) > 0 ){
			 		
			 		$typeQuery = "st." . BoaConstans::SERVICE_COLUMN_ID . " in(select sspt." . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . " from " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " sspt where " .
			 		BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . " in ( select spt." . BoaConstans::SERVICE_PART_COLUMN_ID . " from " . BoaConstans::SERVICE_PART_TABLE . " spt where "; 
			 		
			 		$addOrOperator = false;
			 		foreach($serviceTypes as $type){
			 			if($addOrOperator){
							$typeQuery.=" or ";
						}
						$types .="s";
						$params[] = $type;
						$typeQuery .= "spt." . BoaConstans::SERVICE_PART_COLUMN_TYPE . "=?";
						$addOrOperator = true;
			 		}
			 		
			 		$typeQuery .= " ))";
			 		$this->addToWhere($where, $typeQuery);
			 }
			 
			 if(VariableUtils::isBoolean($this->controls->isPromotion())){
			 	$promotionQuery = null;
			 	if(VariableUtils::getBoolean($this->controls->isPromotion())){
			 		$promotionQuery = "st." . BoaConstans::SERVICE_COLUMN_ID . " in(select pst." . BoaConstans::PROMOTION_SERVICE_COLUMN_SID . " from " . BoaConstans::PROMOTION_SERVICE_TABLE . " pst)";
			 	}else{
			 		$promotionQuery = "st." . BoaConstans::SERVICE_COLUMN_ID . " not in(select pst." . BoaConstans::PROMOTION_SERVICE_COLUMN_SID . " from " . BoaConstans::PROMOTION_SERVICE_TABLE . " pst)";
			 	}
			 	$this->addToWhere($where, $promotionQuery);
			 }
			 
			 if(VariableUtils::isBoolean($this->controls->isActive())){
			 	$types .="s";
				$params[] = VariableUtils::getBoolean($this->controls->isActive());
				$this->addToWhere($where, "st." . BoaConstans::SERVICE_COLUMN_ACTIVE . "=?");				
			 }
			 
			if($where != null){
				$query.=" where " . $where;				
			}
			
			$stmt = $connection->prepare($query);
			if($types != null && $types != ""){
				$bind_param = array();
				$bind_param[] = $types;
				for($i=0;$i<sizeof($params);$i++){
					$bind_param[] = &$params[$i];
				}
				call_user_func_array(array($stmt, 'bind_param'), $bind_param);
			}
			$stmt->execute();

			$id = null;	
			$name = null;
			$descr = null;
			$active = null;
			$period = null;
			$price = null;			
			$stmt->bind_result($id, $name, $descr, $active, $period, $price);
			while($stmt->fetch()){
				$result[] = ServiceConverter::convert($id, $name, $descr, $active, $period, $price);
			}
			$stmt->close();
			
			$spsc = new ServicePartSearchControls();
			foreach($result as $service){
				$spsc->setSid($service->getId());
				$spfc = new ServicePartFindCommand($spsc);
				$parts = $spfc->doExecute($connection);
				foreach($parts as $part){
					$service->addPart($part);
				}
			}
			return $result;
		}
		
		private function addToWhere(&$where, $condition){
			if($where == null){
					$where = $condition;
			}else{
				$where.= " and " . $condition;
			}
		}
	}
	
	class ServiceAddCommand implements Command{
		private $service;
		public function __construct(Service $service){
			$this->service = $service;
		}
		
		
		public function doExecute($connection){
			
			if($this->service == null || !VariableUtils::isNumber($this->service->getPeriod()) || !VariableUtils::isNumber($this->service->getPrice())){
				return false;
			}
			
			$query = "insert into " . BoaConstans::SERVICE_TABLE . "(" .
			BoaConstans::SERVICE_COLUMN_ACTIVE . ", " .
			BoaConstans::SERVICE_COLUMN_DESCR . ", " .
			BoaConstans::SERVICE_COLUMN_NAME   . ", " .
			BoaConstans::SERVICE_COLUMN_PERIOD . ", " .
			BoaConstans::SERVICE_COLUMN_PRICE .
			") values(?,?,?,?,?)";
			$desc = VariableUtils::getRealEscapeString($this->service->getDesc());
			$name  = VariableUtils::getRealEscapeString($this->service->getName());
			$period = $this->service->getPeriod();
			$price = $this->service->getPrice();
			$active = VariableUtils::getBoolean($this->service->isActive());
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("issii", $active, $desc, $name, $period, $price);
			$stmt->execute();
			$serviceId = mysqli_insert_id($connection);
			$stmt->close();

			$this->service->setId($serviceId);
			foreach($this->service->getParts() as $part){
				$sapc = new ServiceAddPartCommand($part, $this->service);
				$sapc->doExecute($connection);
			}
			return $serviceId;
		}
	}
	
	class ServiceGetCommand implements Command{
		private $id;
		public function __construct($id){
			$this->id =$id;
		}
		
		public function doExecute($connection){
			
			$query = "select " .
			BoaConstans::SERVICE_COLUMN_NAME . ", " .
			BoaConstans::SERVICE_COLUMN_DESCR . ", " .
			BoaConstans::SERVICE_COLUMN_ACTIVE . ", " .
			BoaConstans::SERVICE_COLUMN_PERIOD . ", " .
			BoaConstans::SERVICE_COLUMN_PRICE . 
			" from  " . BoaConstans::SERVICE_TABLE. " where " . BoaConstans::SERVICE_COLUMN_ID. "=?";

			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			
			$name = null;
			$descr = null;
			$active = null;
			$period = null;
			$price = null;			
			$stmt->bind_result($name, $descr, $active, $period, $price);
			$stmt->fetch();
			$result = ServiceConverter::convert($this->id, $name, $descr, $active, $period, $price);				
			$stmt->close();
			
			
			$spsc = new ServicePartSearchControls();
			$spsc->setSid($this->id);
			$spfc = new ServicePartFindCommand($spsc);
			$parts = $spfc->doExecute($connection);
			foreach($parts as $part){
				$result->addPart($part);
			}
			return $result;
		}
		
	}
	
	class ServiceModifyCommand implements Command{
		private $service;
		public function __construct(Service $service){
			$this->service = $service;
		}
		
		public function doExecute($connection){
			if($this->service == null){
				return false;
			}
			
			if($this->service->getId() == null){
				$sac = new ServiceAddCommand($this->service);
				return $sac->doExecute($connection);
			}
			
			$query = "update " . BoaConstans::SERVICE_TABLE . " set " .
			BoaConstans::SERVICE_COLUMN_ACTIVE . "=?, " .
			BoaConstans::SERVICE_COLUMN_DESCR . "=?," .
			BoaConstans::SERVICE_COLUMN_NAME  . "=?, ". 
			BoaConstans::SERVICE_COLUMN_PERIOD . "=?, ".
			BoaConstans::SERVICE_COLUMN_PRICE . "=? where " . 
			BoaConstans::SERVICE_COLUMN_ID . "=?";
			$desc = VariableUtils::getRealEscapeString($this->service->getDesc());
			$name  = VariableUtils::getRealEscapeString($this->service->getName());
			$period = $this->service->getPeriod();
			$price =  $this->service->getPrice();
			$id = $this->service->getId();
			$active = VariableUtils::getBoolean($this->service->isActive());
			if(!VariableUtils::isNumber($id) || !VariableUtils::isNumber($period) || !VariableUtils::isNumber($price)){
				return false;
			}
			$stmt = $connection->prepare($query);
			$stmt->bind_param("issiii",$active, $desc, $name, $period, $price, $id);
			$stmt->execute();
			$stmt->close();
			$query = "delete from " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " where " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . "=?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i",$id);
			$stmt->execute();
			$stmt->close();
			foreach($this->service->getParts() as $part){
				$sapc = new ServiceAddPartCommand($part, $this->service);
				$sapc->doExecute($connection);
			}
			return true;
		}
	}
	
	class ServiceDeleteCommand implements Command{
		private $id;
		public function __construct($id){
			$this->id = $id;
		}
		public function doExecute($connection){
			if(!VariableUtils::isNumber($this->id)){
				return false;
			}
			
			$query = "delete from " . BoaConstans::SERVICE_TABLE. " where " . BoaConstans::SERVICE_COLUMN_ID . "= ?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$stmt->close();
			
			
			$query = "delete from " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " where " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . " =?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$stmt->close();
			
			return true;
		}
	}
	
	class ServiceAddPartCommand implements Command{
		private $service;
		private $servicePart;
		public function __construct(ServicePart $servicePart, Service $service){
			$this->service =$service;
			$this->servicePart = $servicePart;
		}
		
		public function doExecute($connection){
			if($this->service == null || $this->servicePart == null || !VariableUtils::isNumber($this->service->getId()) || !VariableUtils::isNumber($this->servicePart->getId())){
				return false;
			}
			
			$query = "insert into " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " ( " .
			BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . ", " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . ") values(?,?)";
			$stmt = $connection->prepare($query);
			$sid = $this->service->getId();
			$spid = $this->servicePart->getId();
			$stmt->bind_param("ii", $sid, $spid);
			$stmt->execute();
			$ar = $stmt->affected_rows;
			$stmt->close();	
			return $ar > 0 ? true : false;
		}
			
	}
	
	class ServiceDeletePartCommand implements Command{
		private $serviceId;
		private $servicePartId;
		public function __construct($servicePartId, $serviceId){
			$this->serviceId = $serviceId;
			$this->servicePartId = $servicePartId;
		}
		public function doExecute($connection){
			if(!VariableUtils::isNumber($this->serviceId) || !VariableUtils::isNumber($this->servicePartId)){
				return false;
			}
			
			$query = "delete from " . BoaConstans::SERVICE_SERVICE_PART_TABLE . " where " .
			BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . "=? and " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . "=?"; 
			$stmt = $connection->prepare($query);
			$stmt->bind_param("ii", $this->serviceId, $this->servicePartId);
			$stmt->execute();
			$ar = $stmt->affected_rows;
			$stmt->close();	
			return $ar > 0 ? true : false;
		}	
	}
	
	class AssignPromotionCommand  implements Command{
		private $service;
		private $promotion;
		public function __construct(Service $service , Promotion $promotion){
			$this->service = $service;
			$this->promotion = $promotion;
		}
		
		
		public function doExecute($connection){
			if($this->promotion == null || $this->service==null || !VariableUtils::isNumber($this->promotion->getId()) || !VariableUtils::isNumber($this->service->getId())){
				return false;
			}	
			
			$query = "insert into ". BoaConstans::PROMOTION_SERVICE_TABLE . "(" .
			BoaConstans::PROMOTION_SERVICE_COLUMN_SID . ", " .
			BoaConstans::PROMOTION_SERVICE_COLUMN_PID . ") values(?, ?) ";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("ii", $this->service->getId(), $this->promotion->getId());
			$stmt->execute();
			$stmt->close();
			return mysqli_insert_id($connection);			
		}
	}
	
	class UnassignPromotionCommand implements Command{
		private $service;
		private $promotion;
		public function __construct(Service $service, Promotion $promotion = null){
			$this->service = $service;
			$this->promotion = $promotion;
		}
		
		public function doExecute($connection){
			if($this->service==null || !VariableUtils::isNumber($this->service->getId())){
				return false;
			}
			
			if($this->promotion != null && !VariableUtils::isNumber($this->promotion->getId())){
				return false;
			}

			$query = "delete from " . BoaConstans::PROMOTION_SERVICE_TABLE . " where " . 
			BoaConstans::PROMOTION_SERVICE_COLUMN_SID . "=?";
			
			if($this->promotion != null){
				$query.=", " .BoaConstans::PROMOTION_SERVICE_COLUMN_PID . "=? ";
			}
			
			$stmt = $connection->prepare($query);
			if($this->promotion != null){
				$stmt->bind_param("ii", $this->service->getId(), $this->promotion->getId());
			}else{
				$stmt->bind_param("i", $this->service->getId());
			}
			$stmt->execute();
			$stmt->close();
			return true;			
		}
	}
	
	
	
	// ==========================================================================
	//							SERVICE PART COMMANDS  
	// ==========================================================================
	
	
	class ServicePartConverter{
		
		
		public static function convert($id, $sid, $name, $type){
			$result = new ServicePart();
			$result->setId($id);
			$result->setSid($sid);
			$result->setName($name);
			$result->setType($type);
			return $result;
		}
	}
	
	class ServicePartFindCommand implements Command{
		
		private $controls;
		public function __construct(ServicePartSearchControls $controls){
			$this->controls = $controls;
		}
		
		public function doExecute($connection){
				$result = array();	
				
				$query = "select spt." .
				BoaConstans::SERVICE_PART_COLUMN_ID . ", spt." .
				BoaConstans::SERVICE_PART_COLUMN_NAME . ", spt." .
				BoaConstans::SERVICE_PART_COLUMN_TYPE . 
				" from " .  BoaConstans::SERVICE_PART_TABLE . " spt";

				$where = null;
				$types = "";
				$params = array();
				
				
				if (VariableUtils::isNumber($this->controls->getSid())){
					$types .="i";
					$params[] = $this->controls->getSid();	
					$serviceQuery =   "spt." . BoaConstans::SERVICE_PART_COLUMN_ID.  " in(select sspt." . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . " from " 
					. BoaConstans::SERVICE_SERVICE_PART_TABLE . " sspt where " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SID . "=?)";
					$this->addToWhere($where, $serviceQuery);
				}
				
				if($this->controls->getName() != null){
					$types .="s";
					$params[] = VariableUtils::getRealEscapeString($this->controls->getName());
					$this->addToWhere($where, "spt." . BoaConstans::SERVICE_PART_COLUMN_NAME . " like upper(CONCAT('%', ?, '%'))");
				}
				
				if(VariableUtils::isNumber($this->controls->getType())){
					$types .="i";
					$params[] = $this->controls->getType();
					$this->addToWhere($where, "spt." . BoaConstans::SERVICE_PART_COLUMN_TYPE . "=?");
				}
				
				
				if($where != null){
					$query.=" where " . $where;				
				}
				
				$stmt = $connection->prepare($query);
				if($types != null && $types != ""){
					$bind_param = array();
					$bind_param[] = $types;
					for($i=0;$i<sizeof($params);$i++){
						$bind_param[] = &$params[$i];
					}
					call_user_func_array(array($stmt, 'bind_param'), $bind_param);
				}
				$stmt->execute();
				$id = null;
				$name = null;
				$type = null;
				
				$stmt->bind_result($id, $name, $type);
				while($stmt->fetch()){
					$result[] = ServicePartConverter::convert($id, $this->controls->getSid(), $name, $type);
				}
				$stmt->close();
				return $result;
		}	
		
		private function addToWhere(&$where, $condition){
			if($where == null){
					$where = $condition;
			}else{
				$where.= " and " . $condition;
			}
		}
	}
	
	class ServicePartAddCommand implements Command{
		private $part;
		public function __construct(ServicePart $part){
			$this->part = $part;
		}
		
		public function doExecute($connection){
			if($this->part == null || $this->part->getType() == null || !VariableUtils::isNumber($this->part->getType())){
				return false;
			} 
			
			if($this->part->getName() == null){
				return false;
			}
			
			$name = VariableUtils::getRealEscapeString($this->part->getName());
			$query = "insert into " . BoaConstans::SERVICE_PART_TABLE . "(".BoaConstans::SERVICE_PART_COLUMN_NAME .", ".  BoaConstans::SERVICE_PART_COLUMN_TYPE. ") values(?,?)";
			$stmt = $connection->prepare($query);
			$type = $this->part->getType();
			$stmt->bind_param("ss", $name, $type);
			$stmt->execute();
			$stmt->close();
			
			return mysqli_insert_id($connection);
		}
	}
	
	class ServicePartGetCommand implements Command{
		
		private $id;
		public function __construct($id){
			$this->id = $id;
		}
		
		public function doExecute($connection){
			$query = "select " .
				BoaConstans::SERVICE_PART_COLUMN_NAME . ", " .
				BoaConstans::SERVICE_PART_COLUMN_TYPE . 
				" from " .  BoaConstans::SERVICE_PART_TABLE .  " where " . BoaConstans::SERVICE_PART_COLUMN_ID .  "=?";
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
				
			$name = null;
			$type = null;
			$stmt->bind_result($name, $type);
			$stmt->fetch();
			$stmt->close();
			return ServicePartConverter::convert($this->id, null, $name, $type);	
		}
	}
	
	class ServicePartDeleteCommand implements Command{
		private $id;
		public function __construct($id){
			$this->id = $id;
		}
		public function doExecute($connection){
			if(!VariableUtils::isNumber($this->id)){
				return false;
			}
			
			$query = "delete from " . BoaConstans::SERVICE_SERVICE_PART_TABLE. " where " . BoaConstans::SERVICE_SERVICE_PART_COLUMN_SPID . "= ?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$stmt->close();
			
			$query = "delete from " . BoaConstans::SERVICE_PART_TABLE. " where " . BoaConstans::SERVICE_PART_COLUMN_ID . "= ?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$stmt->close();
			return true;
		}
	}

	class ServicePartModifyCommand implements Command{
		private $part;
		public function __construct(ServicePart $part){
			$this->part = $part;
		}
		
		public function doExecute($connection){
			if($this->part->getId() == null){
				$spac =new ServicePartAddCommand($this->part);
				return $spac->doExecute($connection);	
			}
			
			if($this->part == null || $this->part->getType() == null || !VariableUtils::isNumber($this->part->getType())){
				return false;
			} 
			
			if($this->part->getName() == null){
				return false;
			}
			
			$query = "update " . BoaConstans::SERVICE_PART_TABLE . " set " .  			
				BoaConstans::SERVICE_PART_COLUMN_NAME . "=?, " .
				BoaConstans::SERVICE_PART_COLUMN_TYPE . "=? " .
				" where " . BoaConstans::SERVICE_PART_COLUMN_ID . "=?";

			$name = $this->part->getName();
			$type = $this->part->getType();
			$id = $this->part->getId();
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("sii", $name, $type, $id);
			$stmt->execute();
			$ar = $stmt->affected_rows;
			$stmt->close();
			return $ar > 0 ? true : false;
		}
	}
?>